const { db, query } = require("../database/db")
// 添加用户的model-sql语句
// const createuser = (username,password,email,phone,role_id,created_time,callback)=>{
//     let sql = `INSERT INTO users (username,password,email,phone,role_id,created_time) VALUES (?,?,?,?,?,?)`
//     db.query(sql,[username,password,email,phone,role_id,created_time],(error,result)=>{
//         if(error){
//             callback(error,null)
//         }else{
//             callback(null,result)
//         }
//     })
// }

// 创建用户
// const createuser = (username, password, email, phone, role_id, truename, created_time) => {
//     let sql = `INSERT INTO users (username,password,email,phone,role_id,truename,created_time) VALUES (?,?,?,?,?,?,?)`;
//     return query(sql, [username, password, email, phone, role_id, truename, created_time])
// }

// 检查用户名是否存在
const checkUserExists = async (username) => {
    const sql = 'SELECT id FROM users WHERE username = ?';
    const result = await query(sql, [username]);
    return result.length > 0;
}

// 检查邮箱是否存在
const checkEmailExists = async (email) => {
    const sql = 'SELECT id FROM users WHERE email = ?';
    const result = await query(sql, [email]);
    return result.length > 0;
}

// 创建用户
const createUser = async (username, password, email, phone, role_id, truename) => {
    const sql = `INSERT INTO users 
        (username, password, email, phone, role_id, truename, created_time) 
        VALUES (?, ?, ?, ?, ?, ?, NOW())`;
    return query(sql, [username, password, email, phone, role_id, truename]);
}

// 用户登录的语句
const login = (username, password, callback) => {
    // 最后一个参数callback回调函数
    // 在controller层中接收：bcrypt.hash(password, Mpassword, (err, hash) => { 一一对应的
    let sql1 = `SELECT username, password, id FROM users WHERE username = ?`
    db.query(sql1, [username], (error, result) => {
        if (error) {
            // 回调函数的2个参数：1：错误；2：信息（结果）
            callback(error, null)
        } else {
            // callback(null,result)
            if (result.length > 0) {
                console.log(result)
                let sql2 = `
                SELECT 
                    users.username, users.id, 
                    roles.rolecode
                FROM users 
                LEFT JOIN roles ON users.role_id = roles.id 
                WHERE username = ? AND password = ?`
                db.query(sql2, [username, password], (error, results) => {
                    console.log('miam ', password)
                    if (error) {
                        callback(error, null)
                    } else {
                        console.log('账号密码不一致！上面：', results)
                        if (results.length > 0) {

                            callback(null, results)
                        } else {

                            callback('账号密码不一致！', null)
                        }
                    }
                })
            } else {
                callback('此账号不存在！', null)
            }
        }
    })
}
// 用户的删除----改状态--软删除
const deleteuser_f = (id) => {
    let sql = `UPDATE users SET state = 1 WHERE id = ?`
    return query(sql, [id])
}

// 用户的删除----改状态--软删除
const startuser = (id) => {
    let sql = `UPDATE users SET state = 0 WHERE id = ?`
    return query(sql, [id])
}

// 用户的删除----真删除
const deleteuser_t = (id) => {
    let sql = `DELETE FROM users WHERE id = ?`
    return query(sql, [id])
}
// 用户的列表+搜索页
const userlist = async (username, userrole, page_size, page) => {
    // 基础SQL查询语句，使用JOIN连接users表和roles表
    let listSql = `
        SELECT users.id,users.username, users.email, users.phone, users.role_id, users.state_show, users.truename,
        roles.name as role_name, users.state
        FROM users`;
    // 初始化参数数组
    let params = [];

    // 添加JOIN条件
    listSql += ` JOIN roles ON users.role_id = roles.id`; // 将users表和roles表通过role_id和id字段连接

    // 如果有搜索条件，添加到SQL语句中
    let conditions = [`0 = 0`]; // 默认条件，用户状态为0
    if (username) {
        conditions.push(`users.username LIKE ?`);
        params.push(`%${username}%`);
    }
    if (userrole) {
        conditions.push(`users.role_id = ?`);
        params.push(userrole);
    }

    // 如果有搜索条件，添加WHERE子句
    if (conditions.length > 0) {
        listSql += ` WHERE ${conditions.join(' AND ')}`;
    }

    // 添加分页的SQL语句
    listSql += ` ORDER BY id DESC`;
    listSql += ` LIMIT ? OFFSET ?`;
    // (page - 1) * page_size, page * page_size
    // 添加分页参数
    params.push(page_size);
    params.push((page - 1) * page_size);

    // 计算总数的SQL语句
    let countSql = `SELECT COUNT(*) as count FROM users`;
    if (conditions.length > 0) {
        countSql += ` WHERE ${conditions.map(c => `(${c})`).join(' AND ')}`;
    }

    // 执行查询
    const [usersResult, countResult] = await Promise.all([
        query(listSql, params), // 查询用户列表
        query(countSql, params) // 查询总数
    ]);
    return {
        users: usersResult, // 用户列表
        total: countResult // 总数
        // total: countResult.total[0].count
    };
};
// 用户的修改--get数据
const edit_get = (id) => {
    let sql = "SELECT id,username,truename,phone,email,role_id FROM users WHERE id = ? AND state = 0"
    return query(sql, [Number(id)])
}
// 用户个人信息的获取
const per_edit_get = (id) => {
    let sql = "SELECT username,truename,phone,email FROM users WHERE id = ? AND state = 0"
    return query(sql, [Number(id)])
}
// 用户的修改--set数据
const edit_set = (id, username, email, phone, truename, role_id) => {
    let sql = `UPDATE users 
               SET username = ?, email = ?, phone = ?, truename=?, role_id = ?, updated_time = NOW()
               WHERE id = ?`;
    let params = [username, email, phone, truename, role_id, id];
    return query(sql, params);
};
// 用户个人信息的修改
const per_edit_set = (id, username, email, phone, truename, update_time) => {
    let sql = `UPDATE users 
               SET username = ?, email = ?, phone = ?, truename=?, updated_time = ?
               WHERE id = ?`;
    //    updated_time
    let params = [username, email, phone, truename, update_time, id];
    return query(sql, params);
};
// 用户密码重置
const reset_password = (id) => {
    let Mpassword = md5("123456");
    let sql = `UPDATE users SET password = ? WHERE id = ?`;
    return query(sql, [Mpassword, id]);
}

// 验证旧密码是否正确
const findById = (id) => {
    let sql = `
        SELECT users.password FROM users 
        WHERE id = ?
    `
    return query(sql, [id]);
}
const verifyPassword = (id, password) => {
    let sql = `
        SELECT users.username FROM users 
        WHERE id = ? AND password = ?
    `
    return query(sql, [id, password]);
}
// 用户修改个人密码的接口
const change_pwd = (id, Mpassword) => {
    let sql = `UPDATE users SET password = ? WHERE id = ?`;
    return query(sql, [Mpassword, id]);
}

// 查找用户角色为老师或班主任的用户
const teacher_list = () => {
    let sql = `SELECT id, truename FROM users WHERE role_id IN (2,7) AND state = 0`;
    return query(sql);
}

module.exports = {
    checkUserExists,
    checkEmailExists,
    createUser,
    login,
    deleteuser_f,
    startuser,
    deleteuser_t,
    userlist,
    edit_get,
    per_edit_get,
    edit_set,
    per_edit_set,
    reset_password,
    findById,
    verifyPassword,
    change_pwd,
    teacher_list
}